package ru.hh.school.persistence;

import ru.hh.school.PrivateMessage;
import ru.hh.school.UserData;
import ru.hh.school.exceptions.InnerChatException;
import ru.hh.school.exceptions.MessageNotFoundException;
import ru.hh.school.exceptions.UserNotFoundException;
import ru.hh.school.logging.StaticLogger;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class DataRepository {
    private final ConnectionPool connectionPool;

    public DataRepository(ConnectionPool connectionPool) {
        this.connectionPool = connectionPool;
    }

    private ConnectionFromPool getConnection() throws SQLException, InterruptedException {
        return connectionPool.getConnection();
    }

    public UserData getUserByLoginAndPassword(final String login, final String password) throws InterruptedException {
        ConnectionFromPool connectionFromPool = null;
        String sql = "SELECT id, login, room_id FROM hh_0001_team01.chat_user WHERE login = ? AND password = ?";
        try {
            connectionFromPool = getConnection();
            PreparedStatement ps = connectionFromPool.prepareStatement(sql);
            try {
                ps.setString(1, login);
                ps.setString(2, password);
                ResultSet rs = ps.executeQuery();
                UserData result = null;
                try {
                    if (rs.next()) {
                        int id = rs.getInt(1);
                        String loginFromDB = rs.getString(2);
                        int roomId = rs.getInt(3);
                        result = new UserData(id, loginFromDB, roomId);
                    }
                    return result;
                } finally {
                    rs.close();
                }
            } finally {
                ps.close();
            }
        } catch (SQLException sqle) {
            StaticLogger.getLogger().logError(sqle);
            return null;
        } finally {
            if (connectionFromPool != null) {
                connectionFromPool.close();
            }
        }
    }

    public void setUserRoom(final int userId, final int roomId) throws UserNotFoundException, InterruptedException {
        ConnectionFromPool connectionFromPool = null;
        String sql = "UPDATE hh_0001_team01.chat_user SET room_id = ? WHERE id = ?";
        try {
            connectionFromPool = getConnection();
            PreparedStatement ps = connectionFromPool.prepareStatement(sql);
            try {
                ps.setInt(1, roomId);
                ps.setInt(2, userId);
                int rowCount = ps.executeUpdate();
                if (rowCount == 0) {
                    throw new UserNotFoundException(userId);
                }
            } finally {
                ps.close();
            }
        } catch (SQLException sqle) {
            StaticLogger.getLogger().logError(sqle);
        } finally {
            if (connectionFromPool != null) {
                connectionFromPool.close();
            }
        }
    }

    public List<PrivateMessage> getPrivateMessages(final int recipientId, final long startMessageId, final int limit) throws InnerChatException, InterruptedException {
        ConnectionFromPool connectionFromPool = null;
        String sql = "SELECT id, text FROM hh_0001_team01.private_message WHERE (recipient_id = ?) AND (id > ?) ORDER BY id FETCH FIRST ? ROWS ONLY";
        try {
            connectionFromPool = getConnection();
            PreparedStatement ps = connectionFromPool.prepareStatement(sql);
            try {
                ps.setInt(1, recipientId);
                ps.setLong(2, startMessageId);
                ps.setInt(3, limit);
                ResultSet rs = ps.executeQuery();
                try {
                    List<PrivateMessage> privateMessages = new LinkedList<PrivateMessage>();
                    while (rs.next()) {
                        int id = rs.getInt(1);
                        String text = rs.getString(2);
                        privateMessages.add(new PrivateMessage(id, text, recipientId));
                    }
                    return privateMessages;
                } finally {
                    rs.close();
                }
            } finally {
                ps.close();
            }
        } catch (SQLException sqle) {
            StaticLogger.getLogger().logError(sqle);
            throw new InnerChatException(sqle);
        } finally {
            if (connectionFromPool != null) {
                connectionFromPool.close();
            }
        }
    }

    public int getRecipientIdByPrivateMessageId(final long messageId) throws InterruptedException, InnerChatException, MessageNotFoundException {
        ConnectionFromPool connectionFromPool = null;
        String sql = "SELECT recipient_id FROM hh_0001_team01.private_message WHERE id = ?";
        try {
            connectionFromPool = getConnection();
            PreparedStatement ps = connectionFromPool.prepareStatement(sql);
            try {
                ps.setLong(1, messageId);
                ResultSet rs = ps.executeQuery();
                try {
                    if (rs.next()) {
                        return rs.getInt(1);
                    } else {
                        throw new MessageNotFoundException(messageId);
                    }
                } finally {
                    rs.close();
                }
            } finally {
                ps.close();
            }
        } catch (SQLException sqle) {
            StaticLogger.getLogger().logError(sqle);
            throw new InnerChatException(sqle);
        } finally {
            if (connectionFromPool != null) {
                connectionFromPool.close();
            }
        }
    }

    public long insertPrivateMessage(final String text, final String recipientName) throws UserNotFoundException, InnerChatException, InterruptedException {
        ConnectionFromPool connectionFromPool = null;

        // unfortunately derby doesn't support generated keys for INSERT ... SELECT statement, so we use two queries
        // when we will move to other rdbms do not forget to replace two queries with this one:
        // INSERT INTO hh_0001_team01.private_message (text, recipient_id) SELECT ?, id FROM hh_0001_team01.chat_user WHERE login = ?
        String sql = "INSERT INTO hh_0001_team01.private_message (text, recipient_id) VALUES (?,?)";
        try {
            connectionFromPool = getConnection();
            final int userIdByLogin = getUserIdByLogin(connectionFromPool, recipientName);
            PreparedStatement ps = connectionFromPool.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            try {
                ps.setString(1, text);
                ps.setInt(2, userIdByLogin);
                ps.executeUpdate();
                ResultSet generatedKeysRs = ps.getGeneratedKeys();
                generatedKeysRs.next();
                try {
                    return generatedKeysRs.getLong(1);
                } finally {
                    generatedKeysRs.close();
                }
            } finally {
                ps.close();
            }
        } catch (SQLException sqle) {
            StaticLogger.getLogger().logError(sqle);
            throw new InnerChatException(sqle);
        } finally {
            if (connectionFromPool != null) {
                connectionFromPool.close();
            }
        }
    }

    private int getUserIdByLogin(final ConnectionFromPool connectionFromPool, final String login) throws UserNotFoundException, SQLException {
        String sql = "SELECT id FROM hh_0001_team01.chat_user WHERE login = ?";
        PreparedStatement ps = connectionFromPool.prepareStatement(sql);
        try {
            ps.setString(1, login);
            ResultSet rs = ps.executeQuery();
            try {
                if (rs.next()) {
                    return rs.getInt(1);
                } else {
                    throw new UserNotFoundException(login);
                }
            } finally {
                rs.close();
            }
        } finally {
            ps.close();
        }
    }

    public void deletePrivateMessage(final long messageId) throws InnerChatException, InterruptedException {
        ConnectionFromPool connectionFromPool = null;
        String sql = "DELETE FROM hh_0001_team01.private_message WHERE id = ?";
        try {
            connectionFromPool = getConnection();
            PreparedStatement ps = connectionFromPool.prepareStatement(sql);
            try {
                ps.setLong(1, messageId);
                ps.executeUpdate();
            } finally {
                ps.close();
            }
        } catch (SQLException sqle) {
            StaticLogger.getLogger().logError(sqle);
            throw new InnerChatException(sqle);
        } finally {
            if (connectionFromPool != null) {
                connectionFromPool.close();
            }
        }
    }
}